//package cn.ming.leo.utils;
//
//import cn.ming.leo.utils.Constant.CouponStatus;
//import cn.ming.leo.utils.Constant.CouponType;
//import cn.ming.leo.utils.Constant.Cycle;
//import cn.ming.leo.utils.Constant.InvestStageStatus;
//import cn.ming.leo.utils.Constant.InvestStatus;
//import cn.ming.leo.utils.Constant.ProductStatus;
//import cn.ming.leo.utils.Constant.TicketStatus;
//import cn.ming.leo.utils.Constant.TradeCreditorType;
//import cn.ming.leo.utils.Constant.TradeStatus;
//import cn.ming.leo.utils.Constant.TradeType;
//import cn.ming.leo.utils.Constant.UserCardStatus;
//
//public class SqlStatement {
//
//	/**
//	 * 用户可用体验金
//	 */
//	public final static String Tyj_Usable_Amount = "select sum(amount) amount from ticket where status = "
//			+ TicketStatus.YouXiao.value
//			+ " and user_id = :userId and expire_time >= date_sub(curdate(),interval 0 day) ";
//
//	/**
//	 * 用户累计获取体验金
//	 */
//	public final static String Tyj_Total_Amount = "select sum(amount) amount from ticket where user_id = :userId";
//
//	/**
//	 * 用户累计体验金收益
//	 */
//	public final static String Tyj_Profit_Amount = "select sum(ticket_profit) amount from invest where user_id = :userId and cycle = "
//			+ Cycle.XINSHOU.value;
//
//	/*
//	 * 产品的投资情况
//	 */
//	public final static String Product_Invest_Stats = "select count(distinct(user_id)) userCounter ,sum(amount) totalAmount from invest where product_id = :productId";
//
//	/**
//	 * 体验金购买人数和所有预期收益
//	 */
//	public final static String TYJ_USE_STATS = "select sum(ticket_profit) totalAmount,count(DISTINCT user_id) PeopleCounter"
//			+ " from invest " + " where invest.cycle =  " + Cycle.XINSHOU.value;
//
//	/**
//	 * 已发放的体验金 总额
//	 */
//	public final static String TYJ_PLATFORM_STAT = "select sum(amount) amount ,count(DISTINCT user_id) userCounter from ticket";
//
//	/**
//	 * 个人平台消息未读信息
//	 */
//	public final static String UNREAD_PALTF = "select * from news where type=1 and id not in (select message_id from user_news where user_id= :id) ";
//
//	/**
//	 * 个人平台消息已读
//	 */
//	public final static String READED_PALT = "select * from user_news where message_id in ( :ids ) and user_id= :id";
//
//	/**
//	 * 一个月内交易的金额
//	 */
//	public final static String AMOUNT_TRAND_SUM = "select sum(amount) from trade " + " where type = :type "
//			+ " and user_id = :userid " + " and time > :dateA " + "and time< :dateB and status ="
//			+ Constant.TradeStatus.ChengGong.value;
//	/**
//	 * 一个月内投资的金额
//	 */
//	public final static String AMOUNT_INVEST_SUM = "select sum(amount) from invest " + " where  user_id = :userid "
//			+ " and create_time > :dateA " + "and create_time< :dateB";
//
//	/**
//	 * 一个月内回款的金额
//	 */
//	public final static String AMOUNT_REPAY_SUM = "select sum(t0.principle + t0.interest) from repayment t0 left outer join invest t1 on t1.id = t0.invest_id  where t1.user_id"
//			+ " = :userid " + " and t0.create_time > :dateA " + "and t0.create_time< :dateB";
//
//	/**
//	 * 用户可用体验金
//	 */
//	public final static String USABLE_TYJ = "select sum(amount) from ticket where user_id= :user_id and status = "
//			+ Constant.TicketStatus.YouXiao.value;
//
//	/**
//	 * 用户累计体验金
//	 */
//	public final static String TOTAL_TYJ = "select sum(amount) from ticket where user_id= :user_id";
//
//	/**
//	 * 指定月内在途投资金额总数
//	 */
//	public final static String ZAITU_INVEST_SUM = "select sum(amount) from invest where user_id = :user_id "
//			+ "and create_time>= :createTimeA and create_time <= :createTimeB and cycle != 5 and due_date <= :endTime";
//
//	/**
//	 * 月度提现金额总数
//	 */
//	public final static String MONTH_TRADE_SUM = "select sum(amount) from trade where user_id = :user_id "
//			+ "and time >= :createTimeA and time <= :createTimeB and type =" + Constant.TradeType.TiXian.value;
//
//	/**
//	 * 指定产品的投资金额
//	 */
//	public final static String INVEST_AMOUNT_PRODUCT = "select ifnull(sum(left_amount),0)+ifnull(sum(left_profit),0) amount from invest where product_id = :productId and invest.status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ " ) and status = "
//			+ InvestStatus.ZaiTu.value + " or status = " + InvestStatus.BuFengHuanKuan.value;
//
//	/**
//	 * 指定产品的投资利息
//	 */
//	public final static String INVEST_PROFIT_PRODUCT = "select ifnull(sum(left_profit),0) amount from invest where product_id = :productId and invest.status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value + "," + InvestStatus.TYJDongJie.value + " ) ";
//
//	/**
//	 * 指定产品的投资人数
//	 */
//	public final static String INVEST_USER_PRODUCT = "select count(DISTINCT(user_id)) users from invest where product_id = :productId and invest.status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value + "," + InvestStatus.TYJDongJie.value + " ) ";
//
//	/**
//	 * 指定产品的投资人数
//	 */
//	public final static String USER_INVEST_COUNTER = "select count(*)  counter from invest,product where  user_id = :userId and invest.product_id = product.id "
//			+ "and status = "
//			+ InvestStatus.ZaiTu.value
//			+ " and invest.product_id = product.id and product.type = :type";
//
//	/**
//	 * 平台投资总额
//	 */
//	public final static String INVEST_TOTAL = "select sum(amount) summer  from invest where cycle!=5 and status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value + "," + InvestStatus.TYJDongJie.value + " ) ";
//
//	/**
//	 * 平台投资总额
//	 */
//	public final static String INVEST_TOTAL_NOW = "select ((select sum(amount) from invest where cycle!=5 and status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ " ) and create_time >= '2016-12-12') +(select sum(amount)  from invest where cycle!=5 and create_time < '2016-12-12')) as summer";
//
//	/**
//	 * 平台2016-12-12投资销售总额
//	 */
//	public final static String INVEST_TOTAL_leoILT = "select sum(amount) summer from invest where cycle!=5 and create_time < '2016-12-12'";
//
//	public final static String TICKET_BELONG_TOTAL = "select sum(amount) summer  from ticket where user_id = :userId and belong = :belong";
//
//	/**
//	 * 体验金累计利息
//	 */
//	public final static String TICKET_TOTAL_PROFIT = "select sum(ticket_profit) amounter from invest where cycle = "
//			+ Cycle.XINSHOU.value + " and user_id = :userId";
//
//	/**
//	 * 指定用户的充值总额
//	 */
//	public final static String DEPOSIT_TOTAL_AMOUNT = "select sum(amount) amounter from trade where type = "
//			+ TradeType.ChongZhi.value + " and status = " + TradeStatus.ChengGong.value + " and user_id = :userId";
//
//	/**
//	 * 指定用户的提现总额
//	 */
//	public final static String WITHDRAW_TOTAL_AMOUNT = "select sum(amount) amounter from trade where type = "
//			+ TradeType.TiXian.value + " and status = " + TradeStatus.ChengGong.value + " and user_id = :userId";
//
//	/**
//	 * 提现手续费的金额
//	 */
//	public final static String WITHDRAW_SXF_AMOUNT = "select sum(amount) amounter from trade where type = "
//			+ TradeType.TiXian_SXF.value + " and status in (0,1) and user_id = :userId";
//
//	/**
//	 * 优惠券红包总额度
//	 */
//	public final static String COUPON_REPACK_AMOUNT = "select sum(amount) amounter from user_coupon where type = "
//			+ CouponType.REDPACK.value + " and status =" + CouponStatus.USED.value + " and user_id = :userId";
//
//	/**
//	 * 平台数据1
//	 */
//	public final static String INVEST_TOTAL_NOW_TWOHONR_ONE = "select ((select sum(amount) from invest where cycle!=5 and status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ " ) and create_time >= '2016-12-12' and create_time <= :createTimeB) +"
//			+ " (select sum(amount)  from invest where cycle!=5 and create_time < '2016-12-12' and create_time >= :createTimeA)) as  investTotal  ,"
//			+ " (select sum(current_profit) from invest  where cycle!=5 and  repay_time >= :repayTimeA and  repay_time  < :repayTimeB) as profitTotal,"
//			+ " (select sum(amount) from invest where status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ " ) and cycle !=5  and due_date >= :dueDate and (repay_time >= :dueDate  or repay_time is null) ) as daiShou,"
//			+ " (select count(distinct(user_id)) from invest where status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ " ) and cycle !=5 and create_time >= :createTimeC  and create_time < :createTimeD) as investNum";
//
//	/**
//	 * 平台数据2
//	 */
//	public final static String INVEST_TOTAL_NOW_TWOHONR_TWO = "select ("
//			+ " (select sum(amount)  from invest where cycle!=5 and create_time < :createTimeB and create_time >= :createTimeA)) as  investTotal  ,"
//			+ " (select sum(current_profit) from invest  where cycle!=5 and repay_time >= :repayTimeA and  repay_time  < :repayTimeB) as profitTotal,"
//			+ " (select sum(amount) from invest where status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ " ) and cycle !=5  and due_date >= :dueDate and (repay_time >= :dueDate  or repay_time is null) ) as daiShou,"
//			+ " (select count(distinct(user_id)) from invest where status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value + "," + InvestStatus.TYJDongJie.value
//			+ " ) and cycle !=5 and create_time >= :createTimeC  and create_time < :createTimeD) as investNum";
//
//	/**
//	 * 平台数据3
//	 */
//	public final static String INVEST_TOTAL_NOW_TWOHONR_THREED = "select ((select sum(amount) from invest where cycle!=5 and status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ " ) and create_time >= :createTimeA and create_time <= :createTimeB)) as  investTotal  ,"
//			+ " (select sum(current_profit) from invest  where cycle!=5 and repay_time >= :repayTimeA and  repay_time  < :repayTimeB) as profitTotal,"
//			+ " (select sum(amount) from invest where status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ " ) and cycle !=5 and due_date >= :dueDate and (repay_time >= :dueDate  or repay_time is null) ) as daiShou,"
//			+ " (select count(distinct(user_id)) from invest where status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ " ) and cycle !=5 and create_time >= :createTimeC  and create_time < :createTimeD) as investNum";
//
//	/**
//	 * 投资人数
//	 */
//	public final static String INVEST_NUM = "select count(DISTINCT(user_id)) as investNum from invest where cycle!="
//			+ Cycle.XINSHOU.value + " and status not in (" + InvestStatus.TiQianHuanKuan.value + ","
//			+ InvestStatus.TYJDongJie.value + " ) and create_time >= :createTimeA and create_time < :createTimeB ";
//
//	/**
//	 * 注册人数
//	 */
//	public final static String REGISTER_NUM = "select count(DISTINCT(id)) as registerNum from user_info where register_time >= :registerTimeA and register_time < :registerTimeB";
//
//	/**
//	 * 绑卡人数
//	 */
//	public final static String BINDCARD_NUM = "select count(DISTINCT(user_id)) as bindCardNum from user_card where time >= :timeA and time < :timeB and user_id not in (select id from financier )and status= "
//			+ UserCardStatus.Normal.value;
//
//	/**
//	 * 销售额（投资金额）
//	 */
//	public final static String INVEST_SUM = "select ifnull(sum(amount),0) as investSum from invest where cycle!="
//			+ Cycle.XINSHOU.value + " and status not in ( " + InvestStatus.TiQianHuanKuan.value + ","
//			+ InvestStatus.TYJDongJie.value + " ) and create_time >= :createTimeA and create_time < :createTimeB ";
//
//	/**
//	 * 待收额
//	 */
//	public final static String DAISHOU_SUM = "select ifnull(sum(amount),0) as daiShouSum from invest where status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ " ) and cycle !="
//			+ Cycle.XINSHOU.value + " and due_date >= :createTimeB and create_time <=:createTimeB ";
//
//	/**
//	 * 指定用户的总收益
//	 */
//	public final static String User_Profit = "select sum(current_profit) amount from invest"
//			+ " where user_id = :userId and due_date > :header and due_date < :tailer " + " and status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value + "," + InvestStatus.TYJDongJie.value + " ) ";
//
//	/**
//	 * 指定用户的资金收益
//	 */
//	public final static String User_Asset_Profit = "select sum(current_profit) amount from invest "
//			+ " where user_id = :userId and due_date > :header and due_date < :tailer" + " and cycle !="
//			+ Cycle.XINSHOU.value + " and status not in ( " + InvestStatus.TiQianHuanKuan.value + ","
//			+ InvestStatus.TYJDongJie.value + " ) ";
//
//	/**
//	 * 指定用户的体验金收益
//	 */
//	public final static String User_Tyj_Profit = "select sum(ticket_profit) amount from invest "
//			+ " where user_id = :userId and due_date > :header and due_date < :tailer" + " and cycle ="
//			+ Cycle.XINSHOU.value;
//
//	/**
//	 * 指定用户的用户历史投资（不含体验金）
//	 */
//	public final static String HISTORY_INVEST = "select sum(amount) amount from invest "
//			+ " where user_id = :userId and  cycle !=" + Cycle.XINSHOU.value + " and status = "
//			+ InvestStatus.QuanBuHuanKuan.value;
//
//	/**
//	 * 指定用户的待收本金（在途，不含体验金）
//	 */
//	public final static String WAIT_COLLECT_PRINCIPAL = "select ifnull(sum(left_amount),0) amount from invest "
//			+ " where user_id = :userId and  cycle !=" + Cycle.XINSHOU.value + " and ( status = "
//			+ InvestStatus.ZaiTu.value + " or status =" + InvestStatus.BuFengHuanKuan.value + " )";
//
//	/**
//	 * 指定在投用户的指定投资类型的待收收益（不含体验金）
//	 */
//	public final static String INVEST_CLCLE_PROFIT = "select ifnull(sum(left_profit),0) profit from invest "
//			+ " where user_id = :userId and  cycle = :cycle  and (status = " + InvestStatus.ZaiTu.value
//			+ " or status = " + InvestStatus.BuFengHuanKuan.value + ")";
//
//	/**
//	 * 指定用户的体验金的待收收益
//	 */
//	public final static String INVEST_CLCLE_TYJ_PROFIT = "select sum(ticket_profit) profit from invest "
//			+ " where user_id = :userId and  cycle =" + Cycle.XINSHOU.value + "  and status = "
//			+ InvestStatus.ZaiTu.value;
//
//	/**
//	 * 指定用户的总待收收益（含体验金）
//	 */
//	public final static String WAIT_ALL_PROFIT = "select ifnull(sum(if(cycle=5,current_profit,left_profit)),0) profit from invest "
//			+ " where user_id = :userId  and (status = "
//			+ InvestStatus.ZaiTu.value
//			+ " or status ="
//			+ InvestStatus.BuFengHuanKuan.value + " )";
//
//	/**
//	 * 指定用户的历史收益（含体验金）
//	 */
//	public final static String HISTORY_PROFIT = "select sum(current_profit - ifnull(left_profit,0)) profit from invest "
//			+ " where user_id = :userId  and (status = "
//			+ InvestStatus.QuanBuHuanKuan.value
//			+ " or status ="
//			+ InvestStatus.BuFengHuanKuan.value + " )";
//
//	/**
//	 * 查询距离当前月份的前n月份的总收益（含体验金） 例：n=0 表示当前月份 n = 1 表示上一月份
//	 */
//	public final static String MONTH_SUM_PROFIT = "select sum(current_profit - ifnull(left_profit,0)) profit from invest "
//			+ "  where date_format(repay_time,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL :month MONTH),'%Y-%m') "
//			+ " and user_id = :userId and (status = "
//			+ InvestStatus.QuanBuHuanKuan.value
//			+ " or status = "
//			+ InvestStatus.BuFengHuanKuan.value + " )";
//
//	/**
//	 * 查看当月所有用户投资获取的总收益（含体验金）
//	 */
//	public final static String MONTH_CURRENT_PEOPLE = "select sum(current_profit - ifnull(left_amount,0)) profit from invest "
//			+ " where date_format(repay_time,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH),'%Y-%m') "
//			+ " and ( status = "
//			+ InvestStatus.QuanBuHuanKuan.value
//			+ " or status = "
//			+ InvestStatus.BuFengHuanKuan.value + " )" + " group by user_id ";
//
//	public final static String BigPerson_Counter = "select id from invest " + " where user_id "
//			+ " in (select id from user_info where mobile = :mobile or recommend_user = :mobile) "
//			+ " and status = :status and cycle = :cycle " + "  group by product_id,date(create_time) ";
//
//	public final static String BigPerson_Time_Counter = "select id from invest " + " where user_id "
//			+ " in (select id from user_info where mobile = :mobile or recommend_user = :mobile) "
//			+ " and status = :status and create_time like ' :createTime%' group by product_id,date(create_time) ";
//
//	public final static String BigPerson_Counter31 = "select id from invest " + " where user_id "
//			+ " in (select id from user_info where mobile = :mobile or recommend_user = :mobile) "
//			+ " and status = :status and cycle != :cycle " + "  group by product_id,date(create_time) ";
//
//	public final static String BigPerson_Time_Counter31 = "select id from invest "
//			+ " where user_id "
//			+ " in (select id from user_info where mobile = :mobile or recommend_user = :mobile) "
//			+ " and status = :status and cycle != :cycle and create_time like ' :createTime%' group by product_id,date(create_time) ";
//
//	public final static String BigPerson_Invest = "select i.product_id,i.begin_date,sum(i.amount) amounter, sum(i.origin_profit) originProfit , sum(i.left_profit) leftProfit, "
//			+ " sum(i.ticket_profit) ticketProfit,sum(i.current_profit) currentProfit , i.id from invest i  where i.user_id "
//			+ " in (select id from user_info where mobile = :mobile or recommend_user = :mobile) "
//			+ " and i.status = :status and i.cycle != :cycle group by i.product_id,date(i.create_time) "
//			+ " order by i.create_time desc   limit :limiter offset :offseter ";
//
//	public final static String BigPerson_Time_Invest = "select i.product_id,i.begin_date,sum(i.amount) amounter, sum(i.origin_profit) originProfit , sum(i.left_profit) leftProfit, "
//			+ " sum(i.ticket_profit) ticketProfit,sum(i.current_profit) currentProfit , i.id from invest i where i.user_id "
//			+ " in (select id from user_info where mobile = :mobile or recommend_user = :mobile) "
//			+ " and i.status = :status and i.cycle != :cycle "
//			+ " and i.create_time like :createTime  group by i.product_id,date(i.create_time) "
//			+ " order by i.create_time desc  limit :limiter offset :offseter ";
//
//	/**
//	 * 大额用户的投资记录统计情况
//	 */
//	public final static String BigPerson_Invest_Stat = "select sum(amount) amounter,product_id productId,begin_date begin_date from invest "
//			+ " where user_id "
//			+ " in (select id from user_info where mobile = :mobile or recommend_user = :mobile) "
//			+ " and status = :status and cycle != :cycle  group by product_id,date(create_time)";
//
//	/**
//	 * 大额用户的投资条数
//	 */
//	public final static String BigPerson_Invest_Counter = "select id from invest " + " where user_id "
//			+ " in (select id from user_info where mobile = :mobile or recommend_user = :mobile) "
//			+ " and status = :status and cycle != :cycle group by product_id,date(create_time) ";
//
//	/**
//	 * 大额用户的交易明细-历史收益/预期收益
//	 */
//	public final static String BigPerson_InvestProfix = "select id,product_id,begin_date,sum(amount) amounter from invest "
//			+ " where user_id in (select id from user_info where mobile = :mobile or recommend_user = :mobile) "
//			+ " and status = :status and cycle != :cycle group by product_id,date(create_time) "
//			+ " order by product_id desc limit :limiter offset :offseter ";
//
//	/**
//	 * 大额用户的交易明细-历史收益/预期收益 ----计算总数
//	 */
//	public final static String BigPerson_Total_InvestProfix = "select id from invest "
//			+ " where user_id in (select id from user_info where mobile = :mobile or recommend_user = :mobile) "
//			+ " and status = :status and cycle != :cycle group by product_id,date(create_time) "
//			+ " order by due_date desc ";
//
//	/**
//	 * 所获收益总额
//	 */
//	public final static String Platform_Profit_Total = "select ifnull(sum(" + "dq_profit-dq_pre_profit"
//			+ "+pa_profit-pa_pre_profit" + "+hq_profit-hq_pre_profit" + "+xjq_profit-xjq_pre_profit"
//			+ "+tyj_profit-tyj_pre_profit),0) as profit from user_money ";
//
//	/**
//	 * 所有用户余额
//	 */
//	public final static String LeftAmount_UserMoney = "select sum(left_amount) amount from user_money ";
//
//	/**
//	 * 销售中的产品 已经售出的金额
//	 */
//	public final static String Sale_Left_Amount = "select sum((sale_amount - left_amount)) amount from product where status = "
//			+ ProductStatus.XiaoShouZhong.value;
//
//	/**
//	 * 统计特定类型的交易总额
//	 */
//	public final static String Trade_Amount = "select sum(amount) amount from trade where type = :trade_type and status = :trade_status and time > :param_time ";
//
//	/**
//	 * 还款数据检测
//	 */
//	public final static String Check_Repayment_Data = "select  user_info.id , user_info.name , user_info.mobile , tb.left_amount , tb.total1 , tb.total2 , tb.total3 , tb.total4  from user_info ,"
//			+ " (	select  money.user_id , money.left_amount , money.pa_invest - ifnull(investTotal.totalAmount,0) as total1 ,  money.pa_profit - ifnull(investTotal.totalProfit,0) as total2 ,   money.pa_freeze - ifnull(investTotal.ztAmount,0) as total3 ,  money.pa_pre_profit - ifnull(investTotal.ztProfit,0) as total4  from user_money money left join (	select  att.user_id , att.totalAmount , att.totalProfit ,	 tz.ztAmount ,	 tz.ztProfit	 "
//			+ " from (	select user_id, sum(amount) as totalAmount,sum(current_profit) as totalProfit from invest allInvest where allInvest.cycle != 5 and allInvest.`status` != 30 and allInvest.`status` != -11 group by allInvest.user_id 		) att 		"
//			+ "left join 	(	select user_id, sum(amount) as ztAmount,sum(current_profit) as ztProfit from invest allInvest where allInvest.cycle != 5 and allInvest.`status` = 0 group by allInvest.user_id 		) tz on tz.user_id = att.user_id	) investTotal on investTotal.user_id = money.user_id	where money.user_id NOT IN (	 SELECT	 id	 FROM	 user_info	 WHERE mobile in ( :mobile)	 OR recommend_user in ( :mobile) )) tb  where user_info.id = tb.user_id and (  tb.total1 !=0 or    tb.total2 !=0 or   tb.total3 !=0 or   tb.total4 !=0 )";
//
//	/**
//	 * 用户待激活体验金列表的失效日期
//	 */
//	public final static String Ticket_Hiberbate_Expire_Time = "select expire_time from ticket where status = "
//			+ TicketStatus.Hibernate.value + " and id in ( :ids ) group by expire_time order by expire_time asc";
//
//	/**
//	 * 用户投资总笔数，不包括体验金投资
//	 * 
//	 */
//	public final static String INVEST_COUNT = "select count(*) as 'count' from invest i where i.cycle != :cycle and i.user_id = :user_id ";
//
//	/***
//	 * 某个用户历史充值总额 , 历史提现总额 , 历史提现手续费总额
//	 */
//	public final static String Trade_userable_money_withdraw = "select "
//			+ "(IFNULL((select sum(amount) from trade  where type =" + TradeType.ChongZhi.value
//			+ " and user_id = :userId and status = " + TradeStatus.ChengGong.value + " ),0) -"
//			+ "IFNULL((select sum(amount) from trade where type =" + TradeType.TiXian.value
//			+ " and user_id = :userId  and status !=" + TradeStatus.ShiBai.value + " ),0) -"
//			+ "IFNULL((select sum(amount) from trade where type =" + TradeType.TiXian_SXF.value
//			+ " and user_id = :userId  and status !=" + TradeStatus.Refund_SXF.value + " ),0)) " + "as useableMoney";
//
//	/**
//	 * 授信的总金额，可用余额
//	 */
//	public final static String CREDIT_AMOUNT_SQL = "select ifnull(sum(total_amount),0) totalAmount,ifnull(sum(available_amount),0) availableAmount from credit where user_id = :userId";
//
//	/**
//	 * 电子签章使用情况
//	 */
//	public final static String SIGNATURE_SEAL_SQL = "select count(1) sealCount from signature where user_id = :userId and (seal & :seal) = :sealValue and status not in (1,4);";
//
//	/**
//	 * 我的授信门店列表
//	 */
//	public final static String CREDIT_LIST_SQL = "select id,credit_name creditName,available_amount availableAmount from credit where user_id = :userId and `available_amount` > 0 and available = 0;";
//
//	/**
//	 * 分期单期原始--产品收益
//	 */
//	public final static String Stage_Product_Profit = "select ifnull(sum(repay_interest),0) as allProfit from product_stage where no = :no and product_id = :product_id  ";
//
//	/**
//	 * 分期单期实际--产品收益
//	 */
//	public final static String Stage_Product_Real_Profit = "select ifnull(sum(real_repay_interest),0) as allProfit from product_stage where no = :no and product_id = :product_id  ";
//
//	/**
//	 * 分期单期实际--投资收益
//	 */
//	public final static String Stage_Invest_Real_Profit = "select ifnull(sum(real_repay_interest),0) as allProfit from invest_stage where no = :no and product_id = :product_id  ";
//
//	/**
//	 * 分期截止到某期的所有实际--投资收益
//	 */
//	public final static String Stage_Invest_All_Real_Profit = "select ifnull(sum(real_repay_interest),0) as allProfit from invest_stage where no >= :no and product_id = :product_id  ";
//
//	/**
//	 * 指定用户的资金收益，体验金收益 inveset
//	 */
//	public final static String User_All_Profit = "select due_date,count(*) as repayCounter,ifnull(sum(if(cycle=5,0,current_profit)),0) as userAssetProfit ,ifnull(sum(ticket_profit),0) as userTyjProfit ,ifnull(sum(if(cycle=5,ticket_profit,current_profit)),0) as userProfit"
//			+ " from invest where due_date > :header and due_date < :tailer and status not in ( "
//			+ InvestStatus.TiQianHuanKuan.value
//			+ ","
//			+ InvestStatus.TYJDongJie.value
//			+ "  ) and user_id = :userId and (total_stage_no < 2 or total_stage_no is null) group by due_date order by due_date asc";
//
//	/**
//	 * 指定用户的分期收益 invest_stage
//	 */
//	public final static String User_All_Profit_Stage = "select real_repay_time, count(*) as repayCounter,ifnull(sum(real_repay_interest),0) as userProfit from invest_stage where real_repay_time > :header and real_repay_time < :tailer and status !="
//			+ InvestStageStatus.TiQianHuanKuanShiXiao.value + "  and user_id = :userId group by real_repay_time";
//
//	/**
//	 * 指定用户在途资产
//	 */
//	public static final String INVEST_ASSET_SQL = "select ifnull(SUM(ifnull(left_amount,0) + if(cycle=5,current_profit,left_profit)),0) as allAsset from invest where user_id = :userId and (status = 0 or status="
//			+ InvestStatus.BuFengHuanKuan.value + " )";
//
//	/**
//	 * 债转手续费的金额
//	 */
//	public final static String CREDITOR_SXF_AMOUNT = "select ifnull(sum(amount),0) amounter from trade where type = "
//			+ TradeType.Creditor_SXF.value + " and status in (0,1) and user_id = :userId";
//
//	/**
//	 * 债转人--债转交易差额总数
//	 */
//	public final static String TRANSFER_TRADE_BALANCE = "select ifnull(sum(amount),0) amounter from trade_creditor where type = "
//			+ TradeCreditorType.Transfer_Balance.value + " and status in (0,1) and user_id = :userId";
//
//	/**
//	 * 受让人--债转交易差额总数
//	 */
//	public final static String TRANSFEREE_TRADE_BALANCE = "select ifnull(sum(amount),0) amounter from trade_creditor where type = "
//			+ TradeCreditorType.Transferee_Balance.value + " and status in (0,1) and user_id = :userId";
//
//	/**
//	 * 批量更新投资加息表的到期时间
//	 * 
//	 */
//	public final static String BATCH_UPDATE_INVEST_RAISE = "update invest_raise set rate_float_due_date = :dueDate where invest_id in (select id from invest where product_id = :productId);";
//
//	/**
//	 * 提前还款修改投资加息表的InvestId
//	 * 
//	 */
//	public final static String SIGNLE_UPDATE_INVEST_RAISE_INVESTID = "update invest_raise set invest_id = :newInvestId where invest_id = :oldInvestId";
//
//	/**
//	 * 提前还款修改投资加息表的InvestId
//	 * 
//	 */
//	public final static String SIGNLE_UPDATE_INVEST_RAISE_DUEDATE = "update invest_raise set rate_float_due_date = :dueDate where invest_id = :investId";
//
//	/**
//	 * 期限范围分布 都按开售时间算
//	 */
//	public final static String PRODUCT_DAY = "select ifnull(count(*),0) as count from product where DATEDIFF(product.due_date,product.sale_begin_time)<= :max and DATEDIFF(product.due_date,product.sale_begin_time) > :min";
//
//	/**
//	 * 产品的金额范围分布 sale_amount计算
//	 */
//	public final static String PRODUCT_MONEY = "select ifnull(count(*),0) as count from product where sale_amount< :max and sale_amount >= :min";
//
//	/**
//	 * 投资次数分布
//	 */
//	public final static String INVEST_NUM_SCOPE = "select ifnull(count(t.user_id),0) as count from (select user_id  from invest where cycle !="
//			+ Cycle.XINSHOU.value
//			+ "  and status !="
//			+ InvestStatus.TiQianHuanKuan.value
//			+ "  group by user_id having  count(id)<= :max and count(id) > :min) as t";
//
//	/**
//	 * 投资金额分布
//	 */
//	public final static String INVEST_MONEY_SCOPE = "select ifnull(count(*),0) as count from invest where cycle !="
//			+ Cycle.XINSHOU.value + "  and status !=" + InvestStatus.TiQianHuanKuan.value
//			+ " and  amount >= :min and amount < :max ";
//	
//	/**
//	 * 投资总的原始收益总和,单位分
//	 */
//	public final static String INVEST_SUM_ORIGIN = "select SUM(origin_profit*100) totalOriginProfit from invest where product_id = :product_id and `status` = 0";
//;
//}
